package j_util.database.testing;

import j_util.database.DBConnection;
import j_util.database.DatabaseLibrary;
import j_util.database.TableInformation;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/*

    This file is part of Logica-Logike.

    Logica-Logike is free software: you can redistribute it and/or modify
    it under the terms of the GNU Lesser General Public License as published by
    the Free Software Foundation version 3 of the License.

    Logica-Logike is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public License
    along with Logica-Logike.  If not, see <http://www.gnu.org/licenses/>.

 */
public class SpecificDBCreator {
    public static DBConnection connection;

    public static String createPersona() {
        return "create table PERSONA (apellido varchar(100), nombre varchar(100), telefono varchar(25), celular varchar(25), mail varchar(30), DNI int, primary key (DNI))";
    }

    public static String createUsuario() {
        return "create table USUARIO (esAdministrador boolean, contrasena varchar(100), ID varchar(25), primary key (ID))";
    }

    public static String createVendedor() {
        return "create table VENDEDOR (DNI int, ID varchar(25), ordenDeVenta varchar(25), primary key (DNI))";
    }

    public static String insertPersonaData(Persona persona) {
        return "insert into PERSONA values ('" + persona.getApellido() + "', '" + persona.getNombre() + "', '" + persona.getTelefono() + "', '" + persona.getCelular() + "', '" + persona.getMail() + "', '" + persona.getDNI() + "')";
    }

    public static String deletePersonData(Persona persona) {
        StringBuffer statement = new StringBuffer("delete from PERSONA where ");
        boolean ands = false;
        if (persona.getApellido() != null) {
            ands = setAnds(ands, "apellido = '" + persona.getApellido() + "' ", statement);
        }
        if (persona.getNombre() != null) {
            ands = setAnds(ands, "nombre = '" + persona.getNombre() + "' ", statement);
        }
        if (persona.getTelefono() != null) {
            ands = setAnds(ands, "telefono = '" + persona.getTelefono() + "' ", statement);
        }
        if (persona.getCelular() != null) {
            ands = setAnds(ands, "celular = '" + persona.getCelular() + "' ", statement);
        }
        if (persona.getMail() != null) {
            ands = setAnds(ands, "mail = '" + persona.getMail() + "' ", statement);
        }
        if (persona.getDNI() != -1) {
            ands = setAnds(ands, "DNI = '" + persona.getDNI() + "' ", statement);
        }
        return statement.toString();
    }

    public static String modifyPersonaData(Persona previous, Persona persona) {
        StringBuffer statement = new StringBuffer("update PERSONA ");
        boolean ands = false;
        if (persona.getApellido() != null) {
            ands = setComas(ands, "set apellido = '" + persona.getApellido() + "'", statement);
        }
        if (persona.getNombre() != null) {
            ands = setComas(ands, "nombre = '" + persona.getNombre() + "'", statement);
        }
        if (persona.getTelefono() != null) {
            ands = setComas(ands, "telefono = '" + persona.getTelefono() + "'", statement);
        }
        if (persona.getCelular() != null) {
            ands = setComas(ands, "celular = '" + persona.getCelular() + "'", statement);
        }
        if (persona.getMail() != null) {
            ands = setComas(ands, "mail = '" + persona.getMail() + "'", statement);
        }
//        if(persona.getDNI()!=-1){
//            ands = setComas(ands,"DNI = '"+persona.getDNI()+"'",statement);
//        }

        statement.append(" where ");
        ands = false;
        if (persona.getApellido() != null) {
            ands = setComas(ands, "apellido = '" + previous.getApellido() + "'", statement);
        }
        if (persona.getNombre() != null) {
            ands = setComas(ands, "nombre = '" + previous.getNombre() + "'", statement);
        }
        if (persona.getTelefono() != null) {
            ands = setComas(ands, "telefono = '" + previous.getTelefono() + "'", statement);
        }
        if (persona.getCelular() != null) {
            ands = setComas(ands, "celular = '" + previous.getCelular() + "'", statement);
        }
        if (persona.getMail() != null) {
            ands = setComas(ands, "mail = '" + previous.getMail() + "'", statement);
        }
//        if(persona.getDNI()!=-1){
//            ands = setComas(ands,"DNI = '"+previous.getDNI()+"'",statement);
//        }
        System.out.println("" + statement.toString());
        return statement.toString();
    }


    public static Persona[] createPersonas(int cantidad) {
        Persona[] personas = new Persona[cantidad];
        Persona persona;
        for (int j = 0; j < personas.length; j++) {
            persona = new Persona();
            persona.setApellido("Apellido");
            persona.setNombre("Nombre");
            persona.setTelefono("0800");
            persona.setCelular("15-");
            persona.setMail("username@mail.com");
            persona.setDNI(j);
            personas[j] = persona;
        }
        return personas;
    }

    public static Persona[] createPersonas2(int cantidad) {
        Persona[] personas = new Persona[cantidad];
        Persona persona;
        for (int j = 0; j < personas.length; j++) {
            persona = new Persona();
            persona.setApellido("REPLACED");
            persona.setNombre("HA SIDO MODIFICADO");
            persona.setTelefono("0600");
            persona.setCelular("15-444");
            persona.setMail("mail@REPLACE");
            persona.setDNI(j);
            personas[j] = persona;
        }
        return personas;
    }


    private static boolean setAnds(boolean ands, String statement, StringBuffer buffer) {
        if (ands) {
            buffer.append("and ");
        }
        buffer.append(statement);
        return true;
    }

    private static boolean setComas(boolean ands, String statement, StringBuffer buffer) {
        if (ands) {
            buffer.append(", ");
        }
        buffer.append(statement);
        return true;
    }

    public static void createPredefinedConnection() {
        String connectionURL = "jdbc:hsqldb:hsql://localhost:9001/xdb";
        connection = DatabaseLibrary.createConnection(connectionURL, "sa", "");
    }

    public static void disconnect() {
        connection = null;
    }

    public static void createPersonaTable() throws SQLException {
        String table = "";
        table = SpecificDBCreator.createPersona();//TODO CREATE TABLE
        connection.executeUpdateStatement(table);
    }

    public static void dropPersonaTable() throws SQLException {
        String table = "";
        table = "drop Table PERSONA";//TODO DROP TABLE
        connection.executeUpdateStatement(table);
    }

    public static void insertPredefinedData() throws SQLException {
        Persona[] personas = createPersonas(10);
        String table;
        for (int j = 0; j < personas.length; j++) {
            table = SpecificDBCreator.insertPersonaData(personas[j]);     //todo habilitar para ejecutar insercion de datos
            connection.executeUpdateStatement(table);
        }
    }

    public static void modifyPredefinedData() throws SQLException {
        Persona[] personas = createPersonas(10);
        Persona[] personasPrevious = createPersonas2(10);
        String table;
        for (int j = 0; j < personas.length; j++) {
            table = SpecificDBCreator.modifyPersonaData(personas[j], personasPrevious[j]);      //todo habilitar para efectuar modificacion de datos
            connection.executeUpdateStatement(table);
        }
    }

    public static void deletePredefinedData() throws SQLException {
        Persona[] personas = createPersonas2(10);
        String table;
        for (int j = 0; j < personas.length; j++) {
            table = SpecificDBCreator.deletePersonData(personas[j]);      //todo habilitar para ejecutar borrado
            connection.executeUpdateStatement(table);
        }
    }

    public static ResultSet executePredefinedQuery() throws SQLException {
        String statement = "select * from PERSONA where NOMBRE = 'HA SIDO MODIFICADO'";
        Connection sqlConnection = connection.getConnection();
        Statement statementObject = sqlConnection.createStatement();
        return statementObject.executeQuery(statement);
    }

    public static void execute(String statement) throws SQLException {
        Connection sqlConnection = connection.getConnection();
        Statement statementObject = sqlConnection.createStatement();
        statementObject.executeUpdate(statement);
    }

    public static List<TableInformation> getTableNames() {
        List<TableInformation> tableInformationList = new ArrayList<TableInformation>();
        try {
            // Gets the database metadata
            DatabaseMetaData dbmd;
            dbmd = connection.getConnection().getMetaData();
            // Specify the type of object; in this case we want tables
            String[] types = {"TABLE"};
            ResultSet resultSet = dbmd.getTables(null, null, "%", types);

            TableInformation tableInformation;
            // Get the table names
            while (resultSet.next()) {
                tableInformation = new TableInformation();
                // Get the table name
                tableInformation.setTableName(resultSet.getString(3));
                // Get the table's catalog and schema names (if any)
                tableInformation.setTableCatalog(resultSet.getString(1));
                tableInformation.setTableSchema(resultSet.getString(2));
                tableInformationList.add(tableInformation);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return tableInformationList;
    }

    public static List<String> getColumnNames(String tableName) throws SQLException {
        List<String> columnNameList = new ArrayList<String>();
        Statement statement = connection.getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("select * from " + tableName);
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int numColumns = resultSetMetaData.getColumnCount();
        for (int j = 0; j < numColumns; j++) {
            columnNameList.add(resultSetMetaData.getColumnName(j + 1));
        }
        return columnNameList;
    }

    public static List<String> executePredefinedQuery2() throws SQLException {
        Statement statement = connection.getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("select * from PERSONA where NOMBRE = 'HA SIDO MODIFICADO'");
        List<String> list = new ArrayList<String>();
        StringBuffer text = new StringBuffer();
        int numColumns = 0;
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        numColumns = resultSetMetaData.getColumnCount();
        while (resultSet.next()) {
            for (int j = 0; j < numColumns; j++) {
                if (j < numColumns - 1) {
                    text.append("\n" + resultSet.getString((j + 1)));
                } else {
                    text.append("\n" + resultSet.getInt((j + 1)));
                }
            }
            list.add(text.toString());
            text = new StringBuffer();
        }
        return list;
    }

    public static List<String> getPredefinedInformation() throws SQLException {
        Statement statement = connection.getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("select * from PERSONA");
        List<String> list = new ArrayList<String>();
        StringBuffer text = new StringBuffer();
        int numColumns = 0;
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        numColumns = resultSetMetaData.getColumnCount();
        while (resultSet.next()) {
            for (int j = 0; j < numColumns; j++) {
                if (j < numColumns - 1) {
                    text.append("\t" + resultSet.getString((j + 1)));
                } else {
                    text.append("\t" + resultSet.getInt((j + 1)));
                }
            }
            list.add(text.toString());
            text = new StringBuffer();
        }
        return list;
    }

    public static Object[][] getInformation(String tableName) throws SQLException {
        Statement statement = connection.getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery("select * from " + tableName);

        Object[][] array;
        List<Object[]> list = new ArrayList<Object[]>();
        Object[] innerArray;
        int numColumns = 0;
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        numColumns = resultSetMetaData.getColumnCount();
        while (resultSet.next()) {
            innerArray = new Object[numColumns];
            for (int j = 0; j < numColumns; j++) {
                if (j < numColumns - 1) {
                    innerArray[j] = resultSet.getString((j + 1));
                } else {
                    innerArray[j] = resultSet.getInt((j + 1));
                }
            }
            list.add(innerArray);
        }
        array = new Object[list.size()][numColumns];
        for (int j = 0; j < list.size(); j++) {
            array[j] = list.get(j);
        }
        return array;
    }

    public static void main(String[] args) {
        try {
            createPredefinedConnection();
//            createPersonaTable();
//            insertPredefinedData();
//            modifyPredefinedData();

//            ResultSet resultSet = executePredefinedQuery();
//            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
//            int numColumns = resultSetMetaData.getColumnCount();
//            System.out.println("========PRINTING DATA=======");
//            for(int j=0;j<numColumns;j++){
//                System.out.print("\t"+resultSetMetaData.getColumnName(j+1));
//            }
//            while(resultSet.next()){
//                System.out.println("");
//                for(int j=0;j<numColumns;j++){
//                    if(j<numColumns-1){
//                        System.out.print("\t"+resultSet.getString((j+1)));
//                    }else{
//                        System.out.print("\t"+resultSet.getInt((j+1)));
//                    }
//                }
//            }

//            deletePredefinedData();
            dropPersonaTable();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}